<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL基础 | 浪飞IT小栈</title>
    <meta name="generator" content="VuePress 1.9.9">
    <link rel="icon" href="/img/favicon.ico">
    <meta name="description" content="前后端技术分享">
    <meta name="keywords" content="前端博客,后端博客,人工智能,个人技术博客,前端,前端开发,前端框架,web前端,前端面试题,技术文档,学习,面试,JavaScript,js,ES6,TypeScript,vue,python,css3,html5,Node,git,github,markdown,java,Spring,Springboot,Redis,mysql">
    <meta name="baidu-site-verification" content="7F55weZDDc">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/assets/css/0.styles.ada71c49.css" as="style"><link rel="preload" href="/assets/js/app.a2a00aa5.js" as="script"><link rel="preload" href="/assets/js/2.256f807a.js" as="script"><link rel="preload" href="/assets/js/33.4aaeb741.js" as="script"><link rel="prefetch" href="/assets/js/10.ab144fe3.js"><link rel="prefetch" href="/assets/js/11.062e0879.js"><link rel="prefetch" href="/assets/js/12.f9c60882.js"><link rel="prefetch" href="/assets/js/13.0a6ebfcf.js"><link rel="prefetch" href="/assets/js/14.03fc6f12.js"><link rel="prefetch" href="/assets/js/15.19dbf9fd.js"><link rel="prefetch" href="/assets/js/16.6e68160b.js"><link rel="prefetch" href="/assets/js/17.1d678834.js"><link rel="prefetch" href="/assets/js/18.4536fd5c.js"><link rel="prefetch" href="/assets/js/19.9c1b65df.js"><link rel="prefetch" href="/assets/js/20.c5f96e71.js"><link rel="prefetch" href="/assets/js/21.3f0b334e.js"><link rel="prefetch" href="/assets/js/22.55c868be.js"><link rel="prefetch" href="/assets/js/23.91286241.js"><link rel="prefetch" href="/assets/js/24.c1af1909.js"><link rel="prefetch" href="/assets/js/25.a995a755.js"><link rel="prefetch" href="/assets/js/26.abc5dea3.js"><link rel="prefetch" href="/assets/js/27.1eb62850.js"><link rel="prefetch" href="/assets/js/28.b383a871.js"><link rel="prefetch" href="/assets/js/29.088e0d73.js"><link rel="prefetch" href="/assets/js/3.cd716564.js"><link rel="prefetch" href="/assets/js/30.3ccc98d5.js"><link rel="prefetch" href="/assets/js/31.aab7cc23.js"><link rel="prefetch" href="/assets/js/32.5e16b7e6.js"><link rel="prefetch" href="/assets/js/34.45e8e898.js"><link rel="prefetch" href="/assets/js/35.5899c97a.js"><link rel="prefetch" href="/assets/js/36.32e2e263.js"><link rel="prefetch" href="/assets/js/37.c4e27bbd.js"><link rel="prefetch" href="/assets/js/38.fe5e60d2.js"><link rel="prefetch" href="/assets/js/39.2cb0b4ee.js"><link rel="prefetch" href="/assets/js/4.edf4d5e7.js"><link rel="prefetch" href="/assets/js/40.08d5bead.js"><link rel="prefetch" href="/assets/js/41.2f80f24b.js"><link rel="prefetch" href="/assets/js/42.43a49913.js"><link rel="prefetch" href="/assets/js/43.08da7ec2.js"><link rel="prefetch" href="/assets/js/44.7c7cb8a9.js"><link rel="prefetch" href="/assets/js/45.0ddf7e7f.js"><link rel="prefetch" href="/assets/js/46.7c4609a5.js"><link rel="prefetch" href="/assets/js/47.edf3ddf8.js"><link rel="prefetch" href="/assets/js/48.569a90b9.js"><link rel="prefetch" href="/assets/js/49.fa368ac8.js"><link rel="prefetch" href="/assets/js/5.24054156.js"><link rel="prefetch" href="/assets/js/50.b9b3ea7f.js"><link rel="prefetch" href="/assets/js/51.a2ebf0fe.js"><link rel="prefetch" href="/assets/js/52.31f02238.js"><link rel="prefetch" href="/assets/js/53.4110ef1e.js"><link rel="prefetch" href="/assets/js/54.dab741c7.js"><link rel="prefetch" href="/assets/js/55.f27984e9.js"><link rel="prefetch" href="/assets/js/56.bd4c1a8e.js"><link rel="prefetch" href="/assets/js/57.375d40cb.js"><link rel="prefetch" href="/assets/js/58.932e3864.js"><link rel="prefetch" href="/assets/js/59.e657be1b.js"><link rel="prefetch" href="/assets/js/6.b259c061.js"><link rel="prefetch" href="/assets/js/60.88d94274.js"><link rel="prefetch" href="/assets/js/61.fa71d884.js"><link rel="prefetch" href="/assets/js/62.a9d3b3b7.js"><link rel="prefetch" href="/assets/js/63.f849fe75.js"><link rel="prefetch" href="/assets/js/64.1770f7a9.js"><link rel="prefetch" href="/assets/js/65.6afc33db.js"><link rel="prefetch" href="/assets/js/66.799f2eb4.js"><link rel="prefetch" href="/assets/js/67.f36b2f7d.js"><link rel="prefetch" href="/assets/js/68.868cd00b.js"><link rel="prefetch" href="/assets/js/69.4c29b907.js"><link rel="prefetch" href="/assets/js/7.e34ea00f.js"><link rel="prefetch" href="/assets/js/70.1b993ed4.js"><link rel="prefetch" href="/assets/js/71.e16ad4f9.js"><link rel="prefetch" href="/assets/js/72.627505e6.js"><link rel="prefetch" href="/assets/js/73.32768e14.js"><link rel="prefetch" href="/assets/js/74.968a580d.js"><link rel="prefetch" href="/assets/js/75.42d5af3d.js"><link rel="prefetch" href="/assets/js/76.19a69d31.js"><link rel="prefetch" href="/assets/js/77.cf627a78.js"><link rel="prefetch" href="/assets/js/78.07177880.js"><link rel="prefetch" href="/assets/js/8.c4dd00ce.js"><link rel="prefetch" href="/assets/js/9.0ff73be2.js">
    <link rel="stylesheet" href="/assets/css/0.styles.ada71c49.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="/img/logo.png" alt="浪飞IT小栈" class="logo"> <span class="site-name can-hide">浪飞IT小栈</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/pages/6138ae/" class="nav-link">Java速通</a></div><div class="nav-item"><a href="/pages/705b35/" class="nav-link">面试经典</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="精品项目" class="dropdown-title"><a href="/project/" class="link-title">精品项目</a> <span class="title" style="display:none;">精品项目</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>桌面端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/pages/8143cc480faf9a11/" class="nav-link">JavaScript</a></li></ul></li><li class="dropdown-item"><h4>PC后端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/javascript/" class="nav-link">项目1</a></li><li class="dropdown-subitem"><a href="/note/typescript-axios/" class="nav-link">项目二</a></li></ul></li></ul></div></div><div class="nav-item"><a href="/about/" class="nav-link">电子书</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div><div class="nav-item"><a href="/pages/0c07b2/" class="nav-link">IT杂货铺</a></div><div class="nav-item"><a href="/archives/" class="nav-link">更多资源</a></div> <!----></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><div class="blogger"><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/avatar.jpg"> <div class="blogger-info"><h3>浪飞yes</h3> <span>无法简单的人儿~</span></div></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><a href="/pages/6138ae/" class="nav-link">Java速通</a></div><div class="nav-item"><a href="/pages/705b35/" class="nav-link">面试经典</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="精品项目" class="dropdown-title"><a href="/project/" class="link-title">精品项目</a> <span class="title" style="display:none;">精品项目</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>桌面端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/pages/8143cc480faf9a11/" class="nav-link">JavaScript</a></li></ul></li><li class="dropdown-item"><h4>PC后端</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/javascript/" class="nav-link">项目1</a></li><li class="dropdown-subitem"><a href="/note/typescript-axios/" class="nav-link">项目二</a></li></ul></li></ul></div></div><div class="nav-item"><a href="/about/" class="nav-link">电子书</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div><div class="nav-item"><a href="/pages/0c07b2/" class="nav-link">IT杂货铺</a></div><div class="nav-item"><a href="/archives/" class="nav-link">更多资源</a></div> <!----></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>开篇</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>基础语法</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>面向对象</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>常用类</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>基础高级</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>数据库</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/826257/" aria-current="page" class="active sidebar-link">MySQL基础</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/pages/826257/#一、今日学习内容与目标" class="sidebar-link">一、今日学习内容与目标</a></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#二、数据库概述" class="sidebar-link">二、数据库概述</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_2-1-引入" class="sidebar-link">2.1 引入</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_2-2-相关概述" class="sidebar-link">2.2 相关概述</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_2-2-1-数据库-database-db" class="sidebar-link">2.2.1 数据库(DataBase:DB)</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_2-2-2-数据库管理系统-dbms" class="sidebar-link">2.2.2 数据库管理系统 DBMS</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_2-2-3-数据库应用系统" class="sidebar-link">2.2.3 数据库应用系统</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#三、数据库发展历程" class="sidebar-link">三、数据库发展历程</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_3-1-数据库发展流程" class="sidebar-link">3.1 数据库发展流程</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_3-2-常用的-nosql-数据库" class="sidebar-link">3.2 常用的 NoSQL 数据库</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_3-3-常见的关系数据库" class="sidebar-link">3.3 常见的关系数据库</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#四、sql-介绍" class="sidebar-link">四、SQL 介绍</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_4-1-sql-概述" class="sidebar-link">4.1 SQL 概述</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_4-2-sql分类" class="sidebar-link">4.2 SQL分类</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_4-3-书写规则" class="sidebar-link">4.3 书写规则</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#五、表的概述" class="sidebar-link">五、表的概述</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_5-1-什么是表-table" class="sidebar-link">5.1 什么是表 (table)</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_5-2-表和对象的关系-orm" class="sidebar-link">5.2 表和对象的关系(ORM)</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#六、mysql-安装" class="sidebar-link">六、MySQL 安装</a></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#七、mysql-服务" class="sidebar-link">七、MySQL 服务</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_7-1-mysql-服务-了解" class="sidebar-link">7.1 MySQL 服务（了解）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_7-2-连接-mysql-掌握" class="sidebar-link">7.2 连接 MySQL（掌握）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_7-3-navicat-for-mysql" class="sidebar-link">7.3 Navicat for MySQL</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#八、数据库和存储引擎" class="sidebar-link">八、数据库和存储引擎</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_8-1-名词介绍" class="sidebar-link">8.1 名词介绍</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_8-2-数据库分类" class="sidebar-link">8.2 数据库分类</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_8-3-数据库操作-掌握" class="sidebar-link">8.3 数据库操作(掌握)</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_8-4-存储引擎" class="sidebar-link">8.4 存储引擎</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#九、mysql-常用类型" class="sidebar-link">九、MySQL 常用类型</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_9-1-最常用类型-掌握" class="sidebar-link">9.1 最常用类型（掌握）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_9-2-整数类型-了解" class="sidebar-link">9.2 整数类型（了解）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_9-3-小数类型-了解" class="sidebar-link">9.3 小数类型（了解）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_9-4-字符类型-了解" class="sidebar-link">9.4 字符类型（了解）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_9-5-日期类型-了解" class="sidebar-link">9.5 日期类型（了解）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_9-6-二进制类型-了解" class="sidebar-link">9.6 二进制类型（了解）</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#十、表的操作" class="sidebar-link">十、表的操作</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_10-1-创建表-掌握" class="sidebar-link">10.1 创建表（掌握）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_10-2-删除表-掌握" class="sidebar-link">10.2 删除表（掌握）</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_10-3-表的约束-理解" class="sidebar-link">10.3 表的约束（理解）</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#十一、dml-操作" class="sidebar-link">十一、DML 操作</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_11-1-插入操作" class="sidebar-link">11.1 插入操作</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_11-2-修改操作" class="sidebar-link">11.2 修改操作</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_11-3-删除操作" class="sidebar-link">11.3 删除操作</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#十二、dql-查询操作" class="sidebar-link">十二、DQL 查询操作</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/826257/#_12-1-语法说明" class="sidebar-link">12.1 语法说明</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_12-2-导入-product-sql" class="sidebar-link">12.2 导入 product.sql</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_12-3-普通查询" class="sidebar-link">12.3 普通查询</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-3-1-消除结果重复数据" class="sidebar-link">12.3.1 消除结果重复数据</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-3-2-算术运算符" class="sidebar-link">12.3.2 算术运算符</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-3-3-设置别名" class="sidebar-link">12.3.3 设置别名</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-3-4-按格式输出" class="sidebar-link">12.3.4 按格式输出</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_12-4-过滤查询" class="sidebar-link">12.4 过滤查询</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-4-1-比较运算符" class="sidebar-link">12.4.1 比较运算符</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-4-2-逻辑运算符" class="sidebar-link">12.4.2 逻辑运算符</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-4-3-范围和集合" class="sidebar-link">12.4.3 范围和集合</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-4-4-判空" class="sidebar-link">12.4.4 判空</a></li><li class="sidebar-sub-header level4"><a href="/pages/826257/#_12-4-5-模糊匹配查询" class="sidebar-link">12.4.5_模糊匹配查询</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_12-5-结果排序" class="sidebar-link">12.5 结果排序</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_12-6-mysql-的分页查询" class="sidebar-link">12.6 MySQL 的分页查询</a></li><li class="sidebar-sub-header level3"><a href="/pages/826257/#_12-7-统计函数" class="sidebar-link">12.7 统计函数</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#十三、数据备份和恢复" class="sidebar-link">十三、数据备份和恢复</a></li><li class="sidebar-sub-header level2"><a href="/pages/826257/#十四、小结与作业" class="sidebar-link">十四、小结与作业</a></li></ul></li><li><a href="/pages/5ae2e1/" class="sidebar-link">JDBC编程</a></li><li><a href="/pages/9980e4/" class="sidebar-link">MyBatis框架初级</a></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>前端技术</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>热门框架</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>前后端分离</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>权限控制</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>NoSQL</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>微服务</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>消息中间件</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>脚手架</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>技术增值</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/categories/?category=Java%E9%80%9F%E9%80%9A" title="分类" data-v-06225672>Java速通</a></li><li data-v-06225672><a href="/categories/?category=%E6%95%B0%E6%8D%AE%E5%BA%93" title="分类" data-v-06225672>数据库</a></li></ul> <div class="info" data-v-06225672><div title="作者" class="author iconfont icon-touxiang" data-v-06225672><a href="https://github.com/langfeiyes" target="_blank" title="作者" class="beLink" data-v-06225672>langfeiyes</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2024-03-03</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">MySQL基础<!----></h1>  <div class="theme-vdoing-content content__default"><h1 id="mysql基础"><a href="#mysql基础" class="header-anchor">#</a> MySQL基础</h1> <h2 id="一、今日学习内容与目标"><a href="#一、今日学习内容与目标" class="header-anchor">#</a> 一、今日学习内容与目标</h2> <ul><li>了解数据库相关的专业名称</li> <li>掌握 SQL 的常用分类</li> <li>理解表的概念</li> <li>理解 ORM 思想</li> <li>掌握 MySQL 安装</li> <li>了解 MySQL 的服务</li> <li>掌握 MySQL 的连接命令</li> <li>掌握数据库的分类和存储引擎的区别</li> <li>掌握 MySQL 中列的常用类型以及对象的Java类型</li> <li>掌握 MySQL 中的 SQL 语法，包括 DDL，DML，DQL</li> <li>掌握一种数据的备份和恢复</li></ul> <h2 id="二、数据库概述"><a href="#二、数据库概述" class="header-anchor">#</a> 二、数据库概述</h2> <h3 id="_2-1-引入"><a href="#_2-1-引入" class="header-anchor">#</a> 2.1 引入</h3> <p><strong>问题</strong> : 为什么要使用数据库来存数据而不用 XML 或者文件呢?</p> <p>举个栗子: 有 100W 条数据,我们可以将他们保存在一个 txt 或者 xml 文档中.但如果想从这些数据中找到某一条数据,只能通过线性搜索,从头到尾去找,效率很低.那我们能不能使用一种快捷高效的方式来组织这些数据,提高查询效率呢? 使用 <strong>数据库,就能大大的提高数据的操作效率.</strong></p> <h3 id="_2-2-相关概述"><a href="#_2-2-相关概述" class="header-anchor">#</a> 2.2 相关概述</h3> <h4 id="_2-2-1-数据库-database-db"><a href="#_2-2-1-数据库-database-db" class="header-anchor">#</a> 2.2.1 数据库(<strong>DataBase:DB</strong>)</h4> <p>数据库是<strong>按照数据结构来组织、存储和管理数据的仓库</strong>。----&gt; 存储和管理数据的仓库.(说白了,数据库还是一些文件,只是这些文件使用了对应的数据结构来组织数据的).</p> <h4 id="_2-2-2-数据库管理系统-dbms"><a href="#_2-2-2-数据库管理系统-dbms" class="header-anchor">#</a> 2.2.2 数据库管理系统 DBMS</h4> <p><strong>DBMS</strong> （Database Management System）：专门用于管理数据库的计算机系统<strong>软件</strong>
数据库管理系统能够为数据库提供数据的定义、建立、维护、查询和统计等操作功能，并完成对数据完整性、安全性进行控制的功能。我们一般说的数据库,就是指的DBMS（数据库服务器：管理多个数据库文件的软件）</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/db.png" alt=""></p> <h4 id="_2-2-3-数据库应用系统"><a href="#_2-2-3-数据库应用系统" class="header-anchor">#</a> 2.2.3 数据库应用系统</h4> <p>DataBase Application System：使用数据库技术的系统；基本上所有的信息管理系统都是数据库应用系统。因为基本上需要用到数据库。</p> <h2 id="三、数据库发展历程"><a href="#三、数据库发展历程" class="header-anchor">#</a> 三、数据库发展历程</h2> <h3 id="_3-1-数据库发展流程"><a href="#_3-1-数据库发展流程" class="header-anchor">#</a> 3.1 数据库发展流程</h3> <p>1.<strong>层次数据库和网状数据库技术阶段</strong>；
使用指针来表示数据之间的联系。</p> <p>2.<strong>关系数据库技术阶段</strong>(<strong>表</strong>,使用表这种关系将数据组织起来的)；
经典的里程碑阶段。代表 DBMS: Oracle、DB2、MySQL、SQL Server等。</p> <p>3.<strong>非关系数据库技术阶段</strong></p> <p>随着大数据的不断发展，关系型数据库在数据模型，性能，拓展伸缩性上存在一个的缺点，所以非关系型的数据库现在成了一个极其热门的新领域，非关系数据库产品的发展非常迅速。</p> <p><strong>非关系型数据库分类</strong>：</p> <ul><li><p>ORDBMS：面向对象数据库技术。</p></li> <li><p>NoSQL  not only sql：结构化数据库技术。</p></li></ul> <h3 id="_3-2-常用的-nosql-数据库"><a href="#_3-2-常用的-nosql-数据库" class="header-anchor">#</a> 3.2 常用的 NoSQL 数据库</h3> <ul><li><p>键值存储数据库：Oracle BDB,Redis,BeansDB</p></li> <li><p>列式存储数据库：HBase,Cassandra,Riak</p></li> <li><p>文档型数据库：MongoDB,CouchDB</p></li> <li><p>图形数据库：Neo4J,InfoGrid,Infinite Graph</p></li></ul> <h3 id="_3-3-常见的关系数据库"><a href="#_3-3-常见的关系数据库" class="header-anchor">#</a> 3.3 常见的关系数据库</h3> <table><thead><tr><th>数据库系统</th> <th>所属公司</th></tr></thead> <tbody><tr><td>Oracle</td> <td>Oracle</td></tr> <tr><td>DB2</td> <td>IBM</td></tr> <tr><td>SQL Server</td> <td>MS</td></tr> <tr><td>MySQL</td> <td>AB --&gt; SUN --&gt; Oracle</td></tr> <tr><td>Oracle : 运行稳定，可移植性高，功能齐全，性能超群！适用于大型企业领域，但是价格昂贵。</td> <td></td></tr> <tr><td>DB2   ：速度快、可靠性好，适于海量数据，恢复性极强。适用于大中型企业领域，但是价格昂贵。</td> <td></td></tr> <tr><td>SQL Server : 全面，效率高，界面友好，操作容易，但是不跨平台。适用于中小型企业领域。</td> <td></td></tr> <tr><td>MySQL : 开源，体积小，速度快。适用于中小型企业领域。</td> <td></td></tr></tbody></table> <h2 id="四、sql-介绍"><a href="#四、sql-介绍" class="header-anchor">#</a> 四、SQL 介绍</h2> <h3 id="_4-1-sql-概述"><a href="#_4-1-sql-概述" class="header-anchor">#</a> 4.1 SQL 概述</h3> <p>人和人交流需要语言，人和数据库交流也需要语言，而这个专门特定为程序员和数据库打交道的语言就是 SQL 语言。</p> <p><strong>SQL</strong>：结构化查询语言(Structured Query Language)。是关系型数据库<strong>标准语言</strong>。
<strong>特点</strong>：简单，灵活，功能强大。</p> <h3 id="_4-2-sql分类"><a href="#_4-2-sql分类" class="header-anchor">#</a> 4.2 SQL分类</h3> <p><strong>一：数据查询语言（DQL）</strong>：
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字 <strong>SELECT</strong> 是DQL（也是所有 SQL）用得最多的动词，其他 DQL 常用的保留字有 WHERE，ORDER BY，GROUP BY和HAVING。这些 DQL 保留字常与其他类型的SQL语句一起使用。</p> <p><strong>二：数据操作语言（DML）</strong>：
其语句包括动词 INSERT，UPDATE 和 DELETE。它们分别用于添加，修改和删除表中的行。也称为动作语言。</p> <p>三：事务处理语言（TPL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。</p> <p>四：数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。</p> <p><strong>五：数据定义语言（DDL）</strong>：
其语句包括动词 CREATE 和 DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。</p> <p>六：指针控制语言（CCL）：
它的语句，像 DECLARE CURSOR，FETCH INTO 和 UPDATE WHERE CURRENT 用于对一个或多个表单独行的操作。</p> <h3 id="_4-3-书写规则"><a href="#_4-3-书写规则" class="header-anchor">#</a> 4.3 书写规则</h3> <ul><li>数据库中，SQL 语句大小写不敏感.  如: select  SELECT. SeleCt,为了提高可读性，一般<strong>关键字大写，其他小写</strong>。</li> <li>SQL 语句可单行或多行书写,用分号来分辨是否结束; select</li> <li>合理利用空格和缩进使 SQL 语句易读.</li></ul> <h2 id="五、表的概述"><a href="#五、表的概述" class="header-anchor">#</a> 五、表的概述</h2> <h3 id="_5-1-什么是表-table"><a href="#_5-1-什么是表-table" class="header-anchor">#</a> 5.1 什么是表 (table)</h3> <p>我们说 MySQL 是一种关系型数据库。关系数据库最重要的概念就是表。</p> <p>表具有固定的列数和任意的行数，在数学上称为“关系”。</p> <p><strong>注意</strong>: 表定义完,表的结构就固定。</p> <p>学生表(Student)：</p> <table><thead><tr><th>id</th> <th>name</th> <th>age</th></tr></thead> <tbody><tr><td>10</td> <td>小明</td> <td>5</td></tr> <tr><td>20</td> <td>小红</td> <td>5</td></tr></tbody></table> <p>二维表是 <strong>同类实体</strong> 的各种 <strong>属性的集合</strong>，每个实体对应于表中的一行，在关系中称为元组，相当于通常的一条记录；
表中的列表示属性，称为 Field，相当于通常记录中的一个数据项，也叫列、字段。
<strong>行</strong>:  表示一个实体,一条记录
<strong>列</strong>:  字段,数据项</p> <hr> <h3 id="_5-2-表和对象的关系-orm"><a href="#_5-2-表和对象的关系-orm" class="header-anchor">#</a> 5.2 表和对象的关系(ORM)</h3> <p>定义表就确定好结构能存那些属性的值好比定义了类就确定了有哪些属性</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/orm.png" alt=""></p> <p>在开发中，我们需要将表中的数据查询出来保存到内存中，或者把内存中的数据保存到数据库中，此时就需要将数据表的数据和Java中的对象进行映射关联起来。</p> <p>这种映射关联就称为 ORM 思想。</p> <p>ORM： Object Reraltional Mapping : 对象表的映射</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/orm1.png" alt=""></p> <h2 id="六、mysql-安装"><a href="#六、mysql-安装" class="header-anchor">#</a> 六、MySQL 安装</h2> <p><strong>MySQL 安装过程中注意</strong></p> <ul><li>MySQL 的默认端口是:3306</li> <li><strong>数据库默认字符集就是 utf8</strong></li> <li>设置密码 : admin</li> <li>win11 不能使用远程账号</li> <li>系统的用户名不能使用中文</li></ul> <p><strong>数据库操作流程(重点)</strong>:</p> <ol><li><p>保证服务器是开启状态(如果是作为一个系统服务,不需要管).</p></li> <li><p>身份验证:通过账号,密码,数据库地址,端口等等连接数据库</p></li> <li><p>在数据库中选择一个数据库文件.</p></li> <li><p>选择某一张表.</p></li> <li><p>操作表中的数据.</p></li></ol> <p>验证安装成功 -&gt; win -&gt; 输入mysql -&gt; MySQL 自带的客户端-&gt; 输入密码 -&gt;看到 <code>mysql&gt;</code> 说明安装成功</p> <h2 id="七、mysql-服务"><a href="#七、mysql-服务" class="header-anchor">#</a> 七、MySQL 服务</h2> <p>如果 MySQL 服务没有启动,则不能访问 MySQL</p> <h3 id="_7-1-mysql-服务-了解"><a href="#_7-1-mysql-服务-了解" class="header-anchor">#</a> 7.1 MySQL 服务（了解）</h3> <p>打开数据库连接之前，一定要保证 MySQL 服务已经开启了.</p> <ul><li><p><code>net start 服务名字</code>：开启一个服务，如：net start MySQL</p></li> <li><p><code>net stop 服务名字</code>：关闭一个服务器，如：net stop MySQL</p></li></ul> <h3 id="_7-2-连接-mysql-掌握"><a href="#_7-2-连接-mysql-掌握" class="header-anchor">#</a> 7.2 连接 MySQL（掌握）</h3> <ul><li>方式 1：进入 MySQL 自带的客户端, 在命令行中输入密码</li> <li>方式 2：在运行（win + r || cmd 中）中输入命令</li></ul> <p><strong>语法格式</strong>: <code>mysql -u账户 -p密码 -h数据库服务器安装的主机 -P数据库端口</code></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>mysql   -uroot   -padmin  -h127.0.0.1  -P3306
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><code>注</code> 若连接的数据库服务器在本机上，并且端口是 3306，则可以简写。 <code>mysql -uroot -padmin</code></p> <h3 id="_7-3-navicat-for-mysql"><a href="#_7-3-navicat-for-mysql" class="header-anchor">#</a> 7.3 Navicat for MySQL</h3> <p>Navicat for MySQL[1] 是一款强大的 MySQL 数据库管理和开发工具，它为专业开发者提供了一套强大的足够尖端的工具，但对于新用户仍然易于学习。Navicat for MySQL 基于Windows平台，为 MySQL 量身订作，提供类似于 MySQL 的用户管理界面工具。此解决方案的出现，将解放 PHP、J2EE 等程序员以及数据库设计者、管理者的大脑，降低开发成本，为用户带来更高的开发效率。</p> <h2 id="八、数据库和存储引擎"><a href="#八、数据库和存储引擎" class="header-anchor">#</a> 八、数据库和存储引擎</h2> <h3 id="_8-1-名词介绍"><a href="#_8-1-名词介绍" class="header-anchor">#</a> 8.1 名词介绍</h3> <p><strong>数据库对象</strong>： 存储，管理和使用数据的不同结构形式，如：表、视图、存储过程、函数、触发器、事件等。
<strong>数据库</strong>：存储数据库对象的容器。</p> <h3 id="_8-2-数据库分类"><a href="#_8-2-数据库分类" class="header-anchor">#</a> 8.2 数据库分类</h3> <ol><li><p>系统数据库（系统自带的数据库）：不能修改</p> <ul><li>information_schema : 存储数据库对象信息，如：用户表信息，列信息，权限，字符，分区等信息。</li> <li>performance_schema : 存储数据库服务器性能参数信息。</li> <li>mysql : 存储数据库用户权限信息。</li> <li>test : 任何用户都可以使用的测试数据库。</li></ul></li> <li><p>用户数据库（用户自定义的数据库）：一般的，一个项目一个用户数据库。</p></li></ol> <p><strong>数据库对象，数据库，数据库服务器关系</strong></p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/dboperate.png" alt=""><br></p> <h3 id="_8-3-数据库操作-掌握"><a href="#_8-3-数据库操作-掌握" class="header-anchor">#</a> 8.3 数据库操作(掌握)</h3> <ol><li>查看数据库服务器存在哪些数据库.
SHOW DATABASES;</li> <li>使用指定的数据库.
USE database_name;</li> <li>查看指定的数据库中有哪些数据表:
SHOW TABLES;</li> <li>创建指定名称的数据库.
CREATE DATABASE database_name</li> <li>删除数据库.
DROP DATABASE database_name</li></ol> <h3 id="_8-4-存储引擎"><a href="#_8-4-存储引擎" class="header-anchor">#</a> 8.4 存储引擎</h3> <p>MySQL 中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都<strong>使用不同的存储机制、索引技巧、锁定水平并且最终提供不同的功能和能力</strong>。
通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。</p> <p><strong>MySQL 常用存储引擎</strong></p> <ul><li>MyISAM：拥有较高的插入，查询速度，但不支持事务，不支持外键。</li> <li><strong>InnoDB：支持事务，支持外键，支持行级锁定，性能较低</strong>。</li></ul> <p>InnoDB 存储引擎提供了具有<strong>提交、回滚和崩溃恢复能力的事务安全</strong>。但对比 MyISAM，处理效率差，且会占用更多的磁盘空间以保留数据和索引。</p> <p><strong>注意</strong>：一个系统，特别是金融系统，没有事务是很恐怖的事情，一般都要选择 InnoDB.</p> <img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/db-Engine.png"> <p>系统数据库 information_schema 数据库存储的 ENGINES</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/db-Engine2.png" alt=""></p> <h2 id="九、mysql-常用类型"><a href="#九、mysql-常用类型" class="header-anchor">#</a> 九、MySQL 常用类型</h2> <p>列的类型,主要是用来约束这一列数据能够存储何种类型的数据.无约束即可随便存则会乱套</p> <h3 id="_9-1-最常用类型-掌握"><a href="#_9-1-最常用类型-掌握" class="header-anchor">#</a> 9.1 最常用类型（掌握）</h3> <p>以下是以后定义列的最常用类型，需要做数据转存，所以得有对应 Java 中的类型，</p> <table><thead><tr><th style="text-align:left;">MySQL</th> <th>Java</th></tr></thead> <tbody><tr><td style="text-align:left;">INT</td> <td>int/Integer</td></tr> <tr><td style="text-align:left;">BIGINT</td> <td>long/Long</td></tr> <tr><td style="text-align:left;">DECIMAL</td> <td>BigDecimal</td></tr> <tr><td style="text-align:left;">DATE/DATETIME</td> <td>java.util.Date</td></tr> <tr><td style="text-align:left;">VARCHAR</td> <td>String</td></tr> <tr><td style="text-align:left;">BIT</td> <td>boolean/Boolean</td></tr></tbody></table> <h3 id="_9-2-整数类型-了解"><a href="#_9-2-整数类型-了解" class="header-anchor">#</a> 9.2 整数类型（了解）</h3> <p>整数类型有宽度指示器，作用是指定位宽。</p> <p>例：某字段类型为 <strong>INT(3)</strong>,保证少于3个值，从数据库检索出来时能够自动地用 0 填充，需设置填充，默认不填充。</p> <p>宽度指示器不影响列存值的范围。<strong>一般不指定位宽</strong>.</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/number.png" alt=""></p> <h3 id="_9-3-小数类型-了解"><a href="#_9-3-小数类型-了解" class="header-anchor">#</a> 9.3 小数类型（了解）</h3> <p>FLOAT[(s,p)]  或  DOUBLE[(s,p)]  : 小数类型，可存放实型和整型 ,<strong>精度 (p) 和范围 (s)</strong></p> <p>money <strong>double</strong>(5,2): 整数和小数<strong>一共占 5 位</strong>.其中小数占 2 位.<strong>都不够精确</strong>。</p> <p><strong>DECIMAL（10,2）</strong> : 高精度类型，金额货币优先选择。</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/Decimal.png" alt=""></p> <h3 id="_9-4-字符类型-了解"><a href="#_9-4-字符类型-了解" class="header-anchor">#</a> 9.4 字符类型（了解）</h3> <p>char(size)     ：  定长字符，0 - 255字节，size 指 N 个字符数，若插入字符数超过设定长度，会被截取并警告。
<strong>varchar</strong>(size)： 变长字符，0 - 255字节，从 MySQL5 开始支持 65535 个字节，若插入字符数超过设定长度，在非严格模式下会被截取并警告。</p> <img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/string1.png"> <p>一般存储大量的字符串，比如文章的纯文本，可以选用 TEXT 系列类型，这个系列都是变长的。</p> <p><strong>注意</strong>: <strong>在 MySQL 中，字符类型必须指定长度，值要使用 单引号引起来。 相当于Java中字符串(String,StringBuilder/StringBuffer);</strong></p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/string2.png" alt=""></p> <h3 id="_9-5-日期类型-了解"><a href="#_9-5-日期类型-了解" class="header-anchor">#</a> 9.5 日期类型（了解）</h3> <p>常用日期和时间类型： <strong>DATE、DATETIME</strong></p> <p><strong>注意: 在 MySQL 中，日期时间值使用单引号引起来。 相当于 Java中 Date，Calender。</strong></p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/date.png" alt=""></p> <h3 id="_9-6-二进制类型-了解"><a href="#_9-6-二进制类型-了解" class="header-anchor">#</a> 9.6 二进制类型（了解）</h3> <p>二进制类型主要用于存放图形、声音和影像，二进制对象，0-4GB。</p> <p><strong>开发中，我们一般存储二进制文件保存路径,所以以上的类型非特殊需求不会使用。</strong></p> <p><strong>BIT，一般存储 0 或 1，存储是 Java 中的 boolean/Boolean 类型的值（需要使用）。</strong></p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/binary.png" alt=""></p> <h2 id="十、表的操作"><a href="#十、表的操作" class="header-anchor">#</a> 十、表的操作</h2> <p>表的操作主要是使用 DDL 来创建表和删除表等操作</p> <h3 id="_10-1-创建表-掌握"><a href="#_10-1-创建表-掌握" class="header-anchor">#</a> 10.1 创建表（掌握）</h3> <p><strong>步骤 :</strong></p> <ol><li>进入要建表的数据库</li> <li>输入建表 SQL 语句</li></ol> <p><strong>建表语法</strong>：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>CREATE TABLE 表名 (
   列名1    列的类型   [约束],
   列名2    列的类型   [约束],
   ....
   列名N    列的类型   约束   
);
-- 注意:最后一行没有逗号
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p><strong>例子</strong>：创建一张学生表(t_student) 有 id name email age</p> <div class="language-MYSQL line-numbers-mode"><pre class="language-text"><code>CREATE TABLE t_student(
  	id BIGINT,
    name VARCHAR(25),
    email VARCHAR(25),
    age INT
);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>**注意 : ** 创建表时,不能使用 MySQL 的关键字,保留字.</p> <p><strong>问题</strong>： 定义一个订单表 order,order 是数据库中排序的关键字，无法直接创建成功.</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 反例
CREATE TABLE `order` (
   	id    BIGINT,
   	name  VARCHAR(15),
    email VARCHAR(25),
    age   INT
);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><p><strong>解决</strong>：</p> <ol><li>尽量避免使用关键字,可以使用其他的单词或单词组合来代替</li> <li>一般情况下,创建表的时候习惯使用 t_ 做表名的开头</li> <li>使用反引号 `  将表名括起来就 ok  order</li></ol> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>CREATE TABLE `order`(
    id        BIGINT,
    name      VARCHAR(25),
    email     VARCHAR(25),
    age       INT
);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h3 id="_10-2-删除表-掌握"><a href="#_10-2-删除表-掌握" class="header-anchor">#</a> 10.2 删除表（掌握）</h3> <p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>DROP TABLE 表名;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>实战</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 删除订单表
DROP TABLE `order`;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>注意： 如果表名是数据库的关键字或保留字需要加上反引号 `</p> <h3 id="_10-3-表的约束-理解"><a href="#_10-3-表的约束-理解" class="header-anchor">#</a> 10.3 表的约束（理解）</h3> <p><strong>表的约束(针对于某一列)</strong>:</p> <ol><li>非空约束：NOT NULL，不允许某列的内容为空。</li> <li>设置列的默认值：DEFAULT。</li> <li>唯一约束：UNIQUE，在该表中，该列的内容必须唯一</li> <li><strong>主键约束：PRIMARY KEY</strong>， 非空且唯一。</li> <li><strong>主键自增长：AUTO_INCREMENT</strong>，从 1 开始，步长为 1。</li> <li>外键约束：FOREIGN KEY，A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键)</li></ol> <p><strong>主键设计 :</strong></p> <ol><li>单字段主键，单列作为主键，<strong>建议使用</strong>。</li> <li>复合主键，使用多列充当主键，不建议。</li></ol> <p><strong>主键分为两种 :</strong></p> <ol><li>自然主键: 使用有业务含义的列作为主键 (不推荐使用);</li> <li>代理主键:使用没有业务含义的列作为主键 (<strong>推荐使用</strong>);</li></ol> <p><strong>结论</strong>： 使用单字段的代理主键</p> <p><strong>例子</strong>：创建学生表，id为主键自增，name唯一，email 不为空，age 默认18</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 移除存在的表
DROP TABLE IF EXISTS `t_student`;
-- 创建表
CREATE TABLE t_student(
	id     BIGINT PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(25) UNIQUE,
    email  VARCHAR(25) NOT NULL,
    age    INT DEFAULT 18
);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h2 id="十一、dml-操作"><a href="#十一、dml-操作" class="header-anchor">#</a> 十一、DML 操作</h2> <p>DML 数据操作语句，用户对表的数据做增删改操作。</p> <p>所有的 DML 操作有一个受影响行，表示 SQL 执行，操作了多少行数据。</p> <h3 id="_11-1-插入操作"><a href="#_11-1-插入操作" class="header-anchor">#</a> 11.1 插入操作</h3> <p><strong>语法：</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>INSERT INTO 表名(列1,列2,column3...)  VALUES(值1,值2,value3...);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>需求</strong>：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 1.插入完整数据记录</span>
<span class="token comment">-- 2.插入数据记录一部分</span>
<span class="token comment">-- 3.插入多条数据记录（MySQL特有）</span>
<span class="token comment">-- 4.插入查询结果</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><strong>实战：</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 1.插入完整数据记录
INSERT INTO t_student(name,email,age) VALUES('xiaoming','xiao@',18);
-- 2.插入数据记录一部分
INSERT INTO t_student(name,age) VALUES('xiaodong',19);
-- 3.插入多条数据记录（MySQL特有）
INSERT INTO t_student(name,email,age) VALUES('xiaohong','hong@',17),('xiaohong2','hong2@',17),('xiaohong3','hong@3',17)
-- 4.插入查询结果
INSERT INTO t_student(name,email,age) SELECT name,email,age FROM t_student
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br></div></div><p><strong>注意事项：</strong>  一次插入操作只插入一行，插入多条数据为 MySQL 特有语法</p> <h3 id="_11-2-修改操作"><a href="#_11-2-修改操作" class="header-anchor">#</a> 11.2 修改操作</h3> <p><strong>语法：</strong> 修改满足条件的数据</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>UPDATE 表名 
SET 列1 = 值1, 列2 = 值2, column3 = value3... 
WHERE [条件]
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>实战：</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 将张三改为西门吹水
-- 将 id 为3 的 name 改为叶孤城,email 改为ye@,age 改为100
UPDATE t_student  SET name='西门吹水' WHERE name='张三';
UPDATE t_student SET name='叶孤城' WHERE id=3;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><strong>注意事项：</strong></p> <ol><li><p>如果省略了 where 条件子句，则全表的数据都会被修改。</p></li> <li><p>没有 FROM 关键字</p></li></ol> <h3 id="_11-3-删除操作"><a href="#_11-3-删除操作" class="header-anchor">#</a> 11.3 删除操作</h3> <p><strong>语法：</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>DELETE FROM 表名 WHERE [条件]
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>实战：</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 删除 id 为 2 的学生信息
-- 删除姓名为叶孤城的学生信息
DELETE FROM t_student WHERE id=2;
DELETE FROM t_student WHERE name='叶孤城'
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><strong>注意事项：</strong></p> <ol><li><strong>FROM</strong> 不能写成  FORM</li> <li>如果省略了 WHERE 子句，则全表的数据都会被删除</li></ol> <h2 id="十二、dql-查询操作"><a href="#十二、dql-查询操作" class="header-anchor">#</a> 十二、DQL 查询操作</h2> <h3 id="_12-1-语法说明"><a href="#_12-1-语法说明" class="header-anchor">#</a> 12.1 语法说明</h3> <p><strong>语法</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT 列1,列2,列3... FROM 表名 [WHERE];

-- SELECT   选择要查询的列
-- FROM     提供数据源 (表、视图或其他的数据源)
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p>如果查询列表写  *  则结果列的显示顺序和创建表时的顺序一致。</p> <div class="language-MYSQL line-numbers-mode"><pre class="language-text"><code>SELECT * FROM 表名 [WHERE]
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>可以自己调整顺序，在 select 后边加上要查询的列名。</p> <h3 id="_12-2-导入-product-sql"><a href="#_12-2-导入-product-sql" class="header-anchor">#</a> 12.2 导入 product.sql</h3> <p>直接把 product.sql 拖到 navicat 的 表的显示位置 -&gt;开始</p> <p><img src="https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/product.png" alt=""></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token punctuation">(</span>
    <span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>productName<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>dir_id<span class="token punctuation">`</span></span> <span class="token keyword">bigint</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>salePrice<span class="token punctuation">`</span></span> <span class="token keyword">double</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>supplier<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>brand<span class="token punctuation">`</span></span> <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>cutoff<span class="token punctuation">`</span></span> <span class="token keyword">double</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token identifier"><span class="token punctuation">`</span>costPrice<span class="token punctuation">`</span></span> <span class="token keyword">double</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
    <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">)</span>
<span class="token punctuation">)</span> <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token keyword">InnoDB</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><span class="token number">22</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CHARSET</span><span class="token operator">=</span>utf8<span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'1'</span><span class="token punctuation">,</span> <span class="token string">'罗技M90'</span><span class="token punctuation">,</span> <span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'90.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.50'</span><span class="token punctuation">,</span> <span class="token string">'35.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'罗技M100'</span><span class="token punctuation">,</span> <span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'49.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.90'</span><span class="token punctuation">,</span> <span class="token string">'33.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'罗技M115'</span><span class="token punctuation">,</span> <span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'99.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.60'</span><span class="token punctuation">,</span> <span class="token string">'38.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'4'</span><span class="token punctuation">,</span> <span class="token string">'罗技M125'</span><span class="token punctuation">,</span> <span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'80.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.90'</span><span class="token punctuation">,</span> <span class="token string">'39.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'5'</span><span class="token punctuation">,</span> <span class="token string">'罗技木星轨迹球'</span><span class="token punctuation">,</span> <span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'182.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.80'</span><span class="token punctuation">,</span> <span class="token string">'80.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'6'</span><span class="token punctuation">,</span> <span class="token string">'罗技火星轨迹球'</span><span class="token punctuation">,</span> <span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'349.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.87'</span><span class="token punctuation">,</span> <span class="token string">'290.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'7'</span><span class="token punctuation">,</span> <span class="token string">'罗技G9X'</span><span class="token punctuation">,</span> <span class="token string">'3'</span><span class="token punctuation">,</span> <span class="token string">'680.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.70'</span><span class="token punctuation">,</span> <span class="token string">'470.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'8'</span><span class="token punctuation">,</span> <span class="token string">'罗技M215'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'89.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.79'</span><span class="token punctuation">,</span> <span class="token string">'30.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'9'</span><span class="token punctuation">,</span> <span class="token string">'罗技M305'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'119.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.82'</span><span class="token punctuation">,</span> <span class="token string">'48.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'10'</span><span class="token punctuation">,</span> <span class="token string">'罗技M310'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'135.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.92'</span><span class="token punctuation">,</span> <span class="token string">'69.80'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'11'</span><span class="token punctuation">,</span> <span class="token string">'罗技M505'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'148.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.92'</span><span class="token punctuation">,</span> <span class="token string">'72.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'12'</span><span class="token punctuation">,</span> <span class="token string">'罗技M555'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'275.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.88'</span><span class="token punctuation">,</span> <span class="token string">'140.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'13'</span><span class="token punctuation">,</span> <span class="token string">'罗技M905'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'458.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.88'</span><span class="token punctuation">,</span> <span class="token string">'270.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'14'</span><span class="token punctuation">,</span> <span class="token string">'罗技MX1100'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'550.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.76'</span><span class="token punctuation">,</span> <span class="token string">'300.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'15'</span><span class="token punctuation">,</span> <span class="token string">'罗技M950'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'678.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.78'</span><span class="token punctuation">,</span> <span class="token string">'320.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'16'</span><span class="token punctuation">,</span> <span class="token string">'罗技MX Air'</span><span class="token punctuation">,</span> <span class="token string">'2'</span><span class="token punctuation">,</span> <span class="token string">'1299.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.72'</span><span class="token punctuation">,</span> <span class="token string">'400.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'17'</span><span class="token punctuation">,</span> <span class="token string">'罗技G1'</span><span class="token punctuation">,</span> <span class="token string">'4'</span><span class="token punctuation">,</span> <span class="token string">'155.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.80'</span><span class="token punctuation">,</span> <span class="token string">'49.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'18'</span><span class="token punctuation">,</span> <span class="token string">'罗技G3'</span><span class="token punctuation">,</span> <span class="token string">'4'</span><span class="token punctuation">,</span> <span class="token string">'229.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.77'</span><span class="token punctuation">,</span> <span class="token string">'96.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'19'</span><span class="token punctuation">,</span> <span class="token string">'罗技G500'</span><span class="token punctuation">,</span> <span class="token string">'4'</span><span class="token punctuation">,</span> <span class="token string">'399.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.88'</span><span class="token punctuation">,</span> <span class="token string">'130.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'20'</span><span class="token punctuation">,</span> <span class="token string">'罗技G700'</span><span class="token punctuation">,</span> <span class="token string">'4'</span><span class="token punctuation">,</span> <span class="token string">'699.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.79'</span><span class="token punctuation">,</span> <span class="token string">'278.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token identifier"><span class="token punctuation">`</span>product<span class="token punctuation">`</span></span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'21'</span><span class="token punctuation">,</span> <span class="token string">'光盘'</span><span class="token punctuation">,</span> <span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'10.00'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'罗技'</span><span class="token punctuation">,</span> <span class="token string">'0.80'</span><span class="token punctuation">,</span> <span class="token string">'2.00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br></div></div><p><strong>实战</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询所有货品信息
-- 查询所有货品的 id,productName,salePrice
SELECT * FROM product;
SELECT id,productName,salePrice FROM product;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h3 id="_12-3-普通查询"><a href="#_12-3-普通查询" class="header-anchor">#</a> 12.3 普通查询</h3> <h4 id="_12-3-1-消除结果重复数据"><a href="#_12-3-1-消除结果重复数据" class="header-anchor">#</a> 12.3.1 消除结果重复数据</h4> <p>语法</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT	DISTINCT 列名, ...  FROM 表名;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>实战</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询商品的分类编号
SELECT DISTINCT dir_id FROM product
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h4 id="_12-3-2-算术运算符"><a href="#_12-3-2-算术运算符" class="header-anchor">#</a> 12.3.2 算术运算符</h4> <p>对 number 型数据可以使用算数操作符创建表达式（+  -  *  /）
对 date 型数据可以使用部分算数操作符创建表达式 （+  -）</p> <p><strong>运算符优先级</strong>：
1、乘法和除法的优先级高于加法和减法
2、同级运算的顺序是从左到右
3、表达式中使用&quot;括号&quot;可强行改变优先级的运算顺序</p> <p>实战：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>--  查询所有货品的id，名称和批发价(批发价=卖价*折扣)
--  查询所有货品的id，名称，和各进50个的成本价(成本=costPirce)
--  查询所有货品的id，名称，各进50个，并且每个运费1元的成本
SELECT id,productName,salePrice * cutoff FROM product;
SELECT id,productName,costPrice * 50 FROM product;
SELECT id,productName,(costPrice + 1) * 50 FROM product;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h4 id="_12-3-3-设置别名"><a href="#_12-3-3-设置别名" class="header-anchor">#</a> 12.3.3 设置别名</h4> <p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT 列名 AS 别名 FROM 表名 [WHERE];
SELECT 列名 别名 FROM 表名 [WHERE]
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>作用</strong>：</p> <ol><li>改变列的标题头</li> <li>作为计算结果的含义</li> <li>作为列的别名</li> <li>如果别名中使用特殊字符，或是强制大小写或有空格时都需要加单引号。</li></ol> <p>实战</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>--  查询所有货品的id，名称，各进50个，并且每个运费1元的成本（使用别名）
SELECT id,productName,(costPrice + 1) * 50 1 AS allPrice FROM product
SELECT id,productName,(costPrice + 1) * 50 allPrice FROM product
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="_12-3-4-按格式输出"><a href="#_12-3-4-按格式输出" class="header-anchor">#</a> 12.3.4 按格式输出</h4> <p>为方便用户浏览查询结果数据，有时需要设置查询结果的显示格式，可以使用 CONCAT 函数来	连接字符串。</p> <p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>CONCAT(字符串1,字符串2,...)
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>实战</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询商品的名字和零售价。格式： xxx 商品的零售价为：ooo
SELECT CONCAT(productName,'商品的零售价为：',salePrice) FROM product
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_12-4-过滤查询"><a href="#_12-4-过滤查询" class="header-anchor">#</a> 12.4 过滤查询</h3> <p>使用 WHERE 子句限定返回的记录</p> <p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT  &lt;selectList&gt; 
FROM 	表名
WHERE	条件;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>注意： WHERE 子句在  FROM 子句后</p> <h4 id="_12-4-1-比较运算符"><a href="#_12-4-1-比较运算符" class="header-anchor">#</a> 12.4.1 比较运算符</h4> <p>=,  &gt;  ,  &gt;=  ,  &lt;  ,  &lt;=  ,  !=</p> <p>不等于：&lt;&gt;  等价  !=</p> <p>实战:</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询商品名为 罗技G9X 的货品信息
-- 查询零售价小于等于 200 的所有货品信息
-- 查询批发价大于 350 的货品信息
SELECT * FROM product WHERE productName='罗技G9X';
SELECT * FROM product WHERE salePrice &lt;= 200
SELECT *,salePrice * cutoff allPrice FROM product WHERE salePrice * cutoff &gt; 350
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><p>思考：where 后使用别名不行，总结： SELECT 和 WHERE 的执行顺序</p> <p><strong>查询语句的子句的执行顺序</strong>
1 FROM 子句: 从哪张表中去查询数据
2 WHERE 子句 : 筛选需要哪些行的数据
3 SELECT 子句: 筛选要显示的列</p> <h4 id="_12-4-2-逻辑运算符"><a href="#_12-4-2-逻辑运算符" class="header-anchor">#</a> 12.4.2 逻辑运算符</h4> <table><thead><tr><th>运算符</th> <th>含义</th></tr></thead> <tbody><tr><td>AND (&amp;&amp;)</td> <td>如果组合的条件都是 TRUE，返回 TRUE</td></tr> <tr><td>OR (||)</td> <td>如果组合的条件之一是 TRUE，返回 TRUE</td></tr> <tr><td>NOT (!)</td> <td>如果下面的条件是 FALSE，返回 TRUE，如果是 TRUE ,返回 FALSE</td></tr></tbody></table> <p>实战：</p> <div class="language-MySQL line-numbers-mode"><pre class="language-text"><code>-- 查询零售价在 300-400（包括 300 和 400）之间的货品信息
-- 查询分类编号为2，4 的所有货品信息
-- 查询分类编号不为2的所有商品信息
-- 查询货品零售价大于等于 250 或者成本大于等于 200 的所有货品信息

SELECT * FROM product WHERE salePrice &gt;= 300 AND salePrice &lt;= 400;
SELECT * FROM product WHERE dir_id = 2 OR dir_id=4;
SELECT * FROM product WHERE NOT dir_id = 2;
SELECT * FROM product WHERE salePrice &gt;= 250 OR costPrice &gt;= 200;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><p><strong>运算符优先级</strong>：</p> <p>优先级	        运算符</p> <hr> <p>1	            所有比较运算符
2	            NOT
3	            AND
4	            OR</p> <p><strong>注意：括号将跨越所有优先级规则</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 分析SQL：
SELECT * FROM product WHERE 
NOT productName LIKE '%M%'  AND salePrice &gt; 100 OR dir_id = 2
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p>![](https://cdn.jsdelivr.net/gh/langfeiyes/images-khub/precedence .png)</p> <h4 id="_12-4-3-范围和集合"><a href="#_12-4-3-范围和集合" class="header-anchor">#</a> 12.4.3 范围和集合</h4> <p><strong>范围匹配</strong>：BETWEEN AND 运算符,一般使用在数字类型的范围上。但对于字符数据和日期类型同样可用。需要两个数据。</p> <p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>WHERE 列名 BETWEEN minValue AND maxValue; -- 闭区间
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>实战：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询零售价在300-400 之间的货品信息
-- 查询零售价不在300-400之间的货品信息
SELECT * FROM product WHERE salePrice BETWEEN 300 AND 400;
SELECT * FROM product WHERE  NOT salePrice BETWEEN 300 AND 400;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><strong>集合查询</strong>： 使用 IN 运算符，判断列的值是否在指定的集合中。</p> <p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>WHERE 列名 IN (值1，值2....);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>实战：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询分类编号为2,4的所有货品的信息
-- 查询分类编号不为2,4的所有货品的信息
SELECT * FROM product WHERE dir_id IN (2,4);
SELECT * FROM product WHERE dir_id NOT IN (2,4);
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h4 id="_12-4-4-判空"><a href="#_12-4-4-判空" class="header-anchor">#</a> 12.4.4 判空</h4> <p><strong>IS NULL</strong>: 判断列的值是否为空值，非空字符串，空字符串使用 = 判断</p> <p>语法：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>WHERE 列名 IS NULL;
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>实战：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询商品名为NULL的所有商品信息。
SELECT * FROM product WHERE productName IS NULL;
SELECT * FROM product WHERE supplier =''
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><p><strong>结论</strong>： 使用=来判断只能判断空字符串,不能判断 null 的，而使用 IS NULL 只能判断 null 值，不能判断空字符串</p> <h4 id="_12-4-5-模糊匹配查询"><a href="#_12-4-5-模糊匹配查询" class="header-anchor">#</a> 12.4.5_模糊匹配查询</h4> <p>模糊查询数据使用 LIKE 运算符执行通配查询</p> <p><strong>语法</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>WHERE 列名 Like '%M_'
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><strong>通配符</strong></p> <ul><li><p><code>%</code> ： 表示可有零或多个任意字符</p></li> <li><p><code>_</code>  :  表示需要一个任意字符</p></li></ul> <p><strong>实战</strong>：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询货品名称匹配'%罗技M9_' 的所有货品信息
SELECT * FROM product WHERE productName LIKE '%罗技M9_'
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_12-5-结果排序"><a href="#_12-5-结果排序" class="header-anchor">#</a> 12.5 结果排序</h3> <p>使用 ORDER BY 子句将查询结果进行单列或多列的降序或升序排序</p> <p><strong>语法</strong>：</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">ORDER</span> <span class="token keyword">BY</span> 列<span class="token number">1</span> <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><span class="token punctuation">]</span><span class="token punctuation">,</span>列<span class="token number">2</span> <span class="token punctuation">[</span><span class="token keyword">ASC</span><span class="token operator">|</span><span class="token keyword">DESC</span><span class="token punctuation">]</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li><code>ASC</code> : 升序，缺省</li> <li><code>DESC</code>: 降序</li></ul> <p><code>注</code> ORDER BY 子句出现在 SELECT 语句的最后。</p> <p><strong>排序分类</strong>：</p> <p>单列的排序</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 选择id，货品名称，分类编号,零售价并且按零售价降序排序
SELECT id,productName,dir_id,salePrice FROM product ORDER BY salePrice DESC
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>多列的排序</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 选择id，货品名称，分类编号,零售价先按分类编号降序排序,再按零售价升序排序
SELECT * FROM product ORDER BY dir_id DESC,salePrice ASC
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p>注意: 谁在前面谁先排序。</p> <p><strong>注意</strong>：如果列的别名使用 <code>''</code> 则按此别名进行的排序无效.</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 反例
SELECT id,salePrice 'sp' FROM product ORDER BY 'sp'
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><p><strong>DQL 子句的执行顺序(重点掌握)</strong></p> <ol><li>FROM 子句 : 从哪张表中去查数据</li> <li>WHERE 子句: 筛选需要的行数据</li> <li>SELECT 子句: 筛选需要显示的列的数据</li> <li>ORDER BY 子句: 排序操作</li></ol> <h3 id="_12-6-mysql-的分页查询"><a href="#_12-6-mysql-的分页查询" class="header-anchor">#</a> 12.6 MySQL 的分页查询</h3> <p><strong>分页的条件数据</strong></p> <ul><li>当前页 : currentPage</li> <li>每页显示的最大记录数: pageSize = 3</li></ul> <p><strong>语法</strong></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>SELECT &lt;selectList&gt; 
FROM 表名
[WHERE]   LIMIT ?,?
-- 第一个? : 开始行的索引数 beginIndex
-- 第二个? : 每页显示的最大记录数 pageSize
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br></div></div><p>第一页: SELECT * FROM <code>product</code> LIMIT 0, 3    1   0 1 2
第二页: SELECT * FROM <code>product</code> LIMIT 3, 3    2   3 4 5
第三页: SELECT * FROM <code>product</code> LIMIT 6, 3    3   6 7 8
第四页: SELECT * FROM <code>product</code> LIMIT 9, 3</p> <p>beginIndex = (currentPage - 1)  *  pageSize</p> <p>实战：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 每页显示 3条数据
-- 第一页： SELECT * FROM product LIMIT 0,3
-- 第三页： SELECT * FROM product LIMIT 6,3
-- 第八页： SELECT * FROM product LIMIT 21,3
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><p><strong>思考题</strong></p> <p>带有排序和分页的查询,是先分页还是先排序？
<code>先排序再分页(银行卡操作流水)</code></p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 按照零售价升序排序,设置每页显示5条数据，查询第一页
SELECT * FROM product ORDER BY salePrice LIMIT 0,5
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br></div></div><h3 id="_12-7-统计函数"><a href="#_12-7-统计函数" class="header-anchor">#</a> 12.7 统计函数</h3> <ul><li><p>COUNT(*)  ： 统计表中有多少条记录（只能是 <code>*</code> 或主键列）</p></li> <li><p>SUM(列)    ：汇总列的总和</p></li> <li><p>MAX(列)     :  获取某一列的最大值</p></li> <li><p>MIN(列)     :  获取某一列的最小值</p></li> <li><p>AVG(列)     :  获取列的平均值</p></li></ul> <p>实战：</p> <div class="language-mysql line-numbers-mode"><pre class="language-text"><code>-- 查询货品表中有多少数据
-- 计算所有货品的总的进货价
SELECT COUNT(*) FROM product
SELECT SUM(costPrice) FROM product
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><h2 id="十三、数据备份和恢复"><a href="#十三、数据备份和恢复" class="header-anchor">#</a> 十三、数据备份和恢复</h2> <p><code>忠告: 在企业修改数据库之前先备份</code></p> <p>MySQL 自身的数据库维护</p> <p><strong>通过 cmd 命令进入dos窗口</strong>：</p> <p>1.导出：mysqldump -u账户 -p密码 数据库名称 &gt; 脚本文件存储地址</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code> mysqldump <span class="token parameter variable">-uroot</span> <span class="token parameter variable">-padmin</span> jdbcdemo <span class="token operator">&gt;</span>  D:/jdbcdemo_bak.sql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>2.导入：mysql -u账户 -p密码 数据库名称 &lt;  脚本文件存储地址</p> <div class="language-shell line-numbers-mode"><pre class="language-shell"><code>mysql <span class="token parameter variable">-uroot</span> <span class="token parameter variable">-padmin</span> jdbcdemo <span class="token operator">&lt;</span>   D:/jdbcdemo_bak.sql
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>Navicat 工具的导入和导出：
Navicat 工具的备份和还原： 需要工具支持的格式才可以恢复</p> <h2 id="十四、小结与作业"><a href="#十四、小结与作业" class="header-anchor">#</a> 十四、小结与作业</h2> <ul><li><p>MySQL 安装成功 5.6</p></li> <li><p>掌握 SQL 的分类以及书写规则</p></li> <li><p>理解关系型数据库表的概念，理解 ORM 思想</p></li> <li><p>掌握连接 MySQL 服务器的方法</p></li> <li><p>掌握数据库的操作 SQL 语句-建议5遍</p></li> <li><p>记住数据列的常用类型，以及和Java的类映射</p></li> <li><p>掌握 DDL 语句，创建表以及表中的主键设置和自动增长设置，删除表的SQL-建议5遍</p></li> <li><p>掌握 DML操作-建议5遍</p></li> <li><p>掌握 DQL 操作-建议5遍</p></li> <li><p>掌握统计函数-建议5遍</p></li> <li><p>掌握一种数据备份和恢复的方式-建议5遍</p></li></ul></div></div>  <div class="page-edit"><!----> <div class="tags"><a href="/tags/?tag=MySQL" title="标签">#MySQL</a></div> <!----></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/42948c/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">注解</div></a> <a href="/pages/5ae2e1/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">JDBC编程</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/42948c/" class="prev">注解</a></span> <span class="next"><a href="/pages/5ae2e1/">JDBC编程</a>→
      </span></p></div></div></div> <div class="article-list"><div class="article-title"><a href="/archives/" class="iconfont icon-bi">最近更新</a></div> <div class="article-wrapper"><dl><dd>01</dd> <dt><a href="/pages/0c07b2/"><div>
            IT杂货铺
            <!----></div></a> <span class="date">03-23</span></dt></dl><dl><dd>02</dd> <dt><a href="/pages/705b35/"><div>
            人事百问
            <!----></div></a> <span class="date">03-22</span></dt></dl><dl><dd>03</dd> <dt><a href="/pages/78eb56/"><div>
            MyBatis-Plus
            <!----></div></a> <span class="date">03-12</span></dt></dl> <dl><dd></dd> <dt><a href="/archives/" class="more">更多文章&gt;</a></dt></dl></div></div></main></div> <div class="footer"><div class="icons"><a href="mailto:langfeiyes@163.com" title="发邮件" target="_blank" class="iconfont icon-youjian"></a><a href="https://github.com/langfeiyes" title="GitHub" target="_blank" class="iconfont icon-github"></a><a href="https://music.163.com/#/playlist?id=755597173" title="听音乐" target="_blank" class="iconfont icon-erji"></a></div> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2024-2024
    <span><a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备18007927号-1</a></span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"><div></div></div></div>
    <script src="/assets/js/app.a2a00aa5.js" defer></script><script src="/assets/js/2.256f807a.js" defer></script><script src="/assets/js/33.4aaeb741.js" defer></script>
  </body>
</html>
